"""meeting_tables

Revision ID: 12cb706566b6
Revises: 750a36340e7b
Create Date: 2025-11-01 09:50:55.480727

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision: str = '12cb706566b6'
down_revision: Union[str, Sequence[str], None] = '750a36340e7b'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('system_config',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('type', sa.String(length=20), nullable=False, comment='类型'),
    sa.Column('name', sa.String(length=255), nullable=False, comment='名称'),
    sa.Column('display_name', sa.String(length=255), nullable=True, comment='显示名'),
    sa.Column('value', sa.Text(), nullable=True, comment='值'),
    sa.Column('create_id', sa.INTEGER(), server_default=sa.text('0'), nullable=True, comment='创建人'),
    sa.Column('create_time', sa.TIMESTAMP(), nullable=True, comment='创建时间'),
    sa.Column('update_id', sa.INTEGER(), server_default=sa.text('0'), nullable=True, comment='更新人'),
    sa.Column('update_time', sa.TIMESTAMP(), nullable=True, comment='更新时间'),
    sa.PrimaryKeyConstraint('id'),
    comment='系统配置表'
    )
    op.create_index(op.f('ix_system_config_id'), 'system_config', ['id'], unique=False)
    op.create_index(op.f('ix_system_config_type'), 'system_config', ['type'], unique=False)
    op.create_table('system_log_operate',
    sa.Column('id', sa.INTEGER(), nullable=False, comment='主键'),
    sa.Column('admin_id', sa.INTEGER(), server_default=sa.text('0'), nullable=False, comment='操作人ID'),
    sa.Column('type', sa.String(length=30), server_default='', nullable=False, comment='请求类型: GET/POST/PUT'),
    sa.Column('title', sa.String(length=30), server_default='', nullable=True, comment='操作标题'),
    sa.Column('ip', sa.String(length=30), server_default='', nullable=False, comment='请求IP'),
    sa.Column('url', sa.String(length=200), server_default='', nullable=False, comment='请求接口'),
    sa.Column('method', sa.String(length=200), server_default='', nullable=False, comment='请求方法'),
    sa.Column('args', sa.Text(), nullable=True, comment='请求参数'),
    sa.Column('error', sa.Text(), nullable=True, comment='错误信息'),
    sa.Column('status', sa.SMALLINT(), server_default=sa.text('1'), nullable=False, comment='执行状态: 1=成功, 2=失败'),
    sa.Column('start_time', sa.INTEGER(), server_default=sa.text('0'), nullable=False, comment='开始时间'),
    sa.Column('end_time', sa.INTEGER(), server_default=sa.text('0'), nullable=False, comment='结束时间'),
    sa.Column('task_time', sa.INTEGER(), server_default=sa.text('0'), nullable=False, comment='执行耗时'),
    sa.Column('create_time', sa.TIMESTAMP(), nullable=True, comment='创建时间'),
    sa.PrimaryKeyConstraint('id'),
    comment='系统操作日志表'
    )
    op.create_table('system_user',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False, comment='名称'),
    sa.Column('email', sa.String(length=255), nullable=False, comment='邮箱'),
    sa.Column('create_id', sa.INTEGER(), server_default=sa.text('0'), nullable=True, comment='创建人'),
    sa.Column('create_time', sa.TIMESTAMP(), nullable=True, comment='创建时间'),
    sa.Column('update_id', sa.INTEGER(), server_default=sa.text('0'), nullable=True, comment='更新人'),
    sa.Column('update_time', sa.TIMESTAMP(), nullable=True, comment='更新时间'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email'),
    comment='用户表'
    )
    op.create_index(op.f('ix_system_user_id'), 'system_user', ['id'], unique=False)
    op.create_table('action_items',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False, comment='行动项主键'),
    sa.Column('meeting_id', sa.Integer(), nullable=False, comment='所属会议'),
    sa.Column('text', sa.String(length=2000), nullable=False, comment='行动项内容'),
    sa.Column('assignee', sa.String(length=255), nullable=True, comment='负责人（用户名或 id）'),
    sa.Column('due_date', sa.DateTime(), nullable=True, comment='截止时间'),
    sa.Column('status', sa.String(length=50), nullable=False, comment='状态 open/in-progress/done'),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.ForeignKeyConstraint(['meeting_id'], ['meetings.id'], ),
    sa.PrimaryKeyConstraint('id'),
    comment='执行表'
    )
    op.create_index(op.f('ix_action_items_meeting_id'), 'action_items', ['meeting_id'], unique=False)
    op.create_table('meetings_recordings',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False, comment='会议录音主键'),
    sa.Column('meeting_id', sa.Integer(), nullable=False, comment='所属会议ID'),
    sa.Column('filename', sa.String(length=512), nullable=True, comment='原始上传文件名'),
    sa.Column('url', sa.String(length=512), nullable=True, comment='文件存放路径'),
    sa.Column('file_type', sa.String(length=128), nullable=True, comment='文件类型'),
    sa.Column('ext', sa.String(length=20), nullable=True, comment='文件扩展'),
    sa.Column('duration_ms', sa.Integer(), nullable=True, comment='音频时长（毫秒）'),
    sa.Column('uploaded_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True, comment='上传时间'),
    sa.Column('processing_status', sa.String(length=50), nullable=False, comment='处理状态: uploaded/processing/done/failed'),
    sa.Column('model_hint', sa.String(length=255), nullable=True, comment='建议用于转录的模型/配置（可由客户端传入）'),
    sa.Column('metadata_json', sa.Text(), nullable=True, comment='JSON 格式的额外元数据（例如设备/采样率等）'),
    sa.ForeignKeyConstraint(['meeting_id'], ['meetings.id'], ),
    sa.PrimaryKeyConstraint('id'),
    comment='会议分段记录表'
    )
    op.create_index(op.f('ix_meetings_recordings_id'), 'meetings_recordings', ['id'], unique=False)
    op.create_index(op.f('ix_meetings_recordings_meeting_id'), 'meetings_recordings', ['meeting_id'], unique=False)
    op.create_table('meetings_summaries',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('meeting_id', sa.Integer(), nullable=False),
    sa.Column('content', sa.Text(), nullable=False, comment='自动生成的摘要内容（可能是 json/markdown）'),
    sa.Column('model_name', sa.String(length=255), nullable=True, comment='用于生成摘要的 LLM/chain 名称'),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True, comment='摘要时间'),
    sa.ForeignKeyConstraint(['meeting_id'], ['meetings.id'], ),
    sa.PrimaryKeyConstraint('id'),
    comment='会议摘要表'
    )
    op.create_index(op.f('ix_meetings_summaries_id'), 'meetings_summaries', ['id'], unique=False)
    op.create_table('participants',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False, comment='参会者主键'),
    sa.Column('meeting_id', sa.Integer(), nullable=False, comment='所属会议ID'),
    sa.Column('user_id', sa.Integer(), nullable=True, comment='用户ID（若为系统用户）'),
    sa.Column('display_name', sa.String(length=200), nullable=True, comment='参会者显示名（可与 user 不同）'),
    sa.Column('role', sa.String(length=50), nullable=True, comment='角色，如 host / attendee / presenter'),
    sa.Column('joined_at', sa.DateTime(), nullable=True, comment='加入会议时间'),
    sa.ForeignKeyConstraint(['meeting_id'], ['meetings.id'], ),
    sa.PrimaryKeyConstraint('id'),
    comment='参会者表'
    )
    op.create_index(op.f('ix_participants_meeting_id'), 'participants', ['meeting_id'], unique=False)
    op.create_table('meetings_transcripts',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False, comment='转录片段主键'),
    sa.Column('meeting_id', sa.Integer(), nullable=False, comment='所属会议'),
    sa.Column('recording_id', sa.Integer(), nullable=True, comment='所属录音'),
    sa.Column('start_ts_ms', sa.Integer(), nullable=False, comment='片段开始时间（相对于录音，毫秒）'),
    sa.Column('end_ts_ms', sa.Integer(), nullable=False, comment='片段结束时间（毫秒）'),
    sa.Column('speaker', sa.String(length=200), nullable=True, comment='说话人标识或标签'),
    sa.Column('text', sa.Text(), nullable=False, comment='转录文本'),
    sa.Column('lang', sa.String(length=32), nullable=True, comment='识别语言代码'),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True, comment='转录创建时间'),
    sa.ForeignKeyConstraint(['meeting_id'], ['meetings.id'], ),
    sa.ForeignKeyConstraint(['recording_id'], ['meetings_recordings.id'], ),
    sa.PrimaryKeyConstraint('id'),
    comment='会议转录表'
    )
    op.create_index(op.f('ix_meetings_transcripts_meeting_id'), 'meetings_transcripts', ['meeting_id'], unique=False)
    op.create_index(op.f('ix_meetings_transcripts_recording_id'), 'meetings_transcripts', ['recording_id'], unique=False)
    op.add_column('meetings', sa.Column('description', sa.Text(), nullable=True, comment='会议描述'))
    op.add_column('meetings', sa.Column('owner_id', sa.Integer(), nullable=True, comment='会议创建者用户ID'))
    op.alter_column('meetings', 'start_time',
               existing_type=mysql.DATETIME(),
               comment='计划开始时间',
               existing_comment='开始时间',
               existing_nullable=True)
    op.alter_column('meetings', 'end_time',
               existing_type=mysql.DATETIME(),
               comment='计划结束时间',
               existing_comment='结束时间',
               existing_nullable=True)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('meetings', 'end_time',
               existing_type=mysql.DATETIME(),
               comment='结束时间',
               existing_comment='计划结束时间',
               existing_nullable=True)
    op.alter_column('meetings', 'start_time',
               existing_type=mysql.DATETIME(),
               comment='开始时间',
               existing_comment='计划开始时间',
               existing_nullable=True)
    op.drop_column('meetings', 'owner_id')
    op.drop_column('meetings', 'description')
    op.drop_index(op.f('ix_meetings_transcripts_recording_id'), table_name='meetings_transcripts')
    op.drop_index(op.f('ix_meetings_transcripts_meeting_id'), table_name='meetings_transcripts')
    op.drop_table('meetings_transcripts')
    op.drop_index(op.f('ix_participants_meeting_id'), table_name='participants')
    op.drop_table('participants')
    op.drop_index(op.f('ix_meetings_summaries_id'), table_name='meetings_summaries')
    op.drop_table('meetings_summaries')
    op.drop_index(op.f('ix_meetings_recordings_meeting_id'), table_name='meetings_recordings')
    op.drop_index(op.f('ix_meetings_recordings_id'), table_name='meetings_recordings')
    op.drop_table('meetings_recordings')
    op.drop_index(op.f('ix_action_items_meeting_id'), table_name='action_items')
    op.drop_table('action_items')
    op.drop_index(op.f('ix_system_user_id'), table_name='system_user')
    op.drop_table('system_user')
    op.drop_table('system_log_operate')
    op.drop_index(op.f('ix_system_config_type'), table_name='system_config')
    op.drop_index(op.f('ix_system_config_id'), table_name='system_config')
    op.drop_table('system_config')
    # ### end Alembic commands ###
